#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
词典单词自动导入脚本
功能：自动将JSON文件导入到数据库
"""

import json
import os
import sys
import pymysql
from pathlib import Path

# 配置
DB_CONFIG = {
    'host': '127.0.0.1',
    'port': 3306,
    'user': 'root',
    'password': '123',
    'database': 'ry-vue',
    'charset': 'utf8mb4'
}

DICT_PATH = '/root/workspace/3.1.0-dev/work/aiword/public/dicts/'

def connect_db():
    """连接数据库"""
    return pymysql.connect(**DB_CONFIG)

def import_dict_words(book_id, book_name, json_file):
    """导入词典单词"""
    try:
        # 读取JSON文件
        with open(json_file, 'r', encoding='utf-8') as f:
            words = json.load(f)
    except Exception as e:
        print(f"   ❌ 读取文件失败: {e}")
        return False
    
    if not isinstance(words, list):
        print(f"   ❌ JSON格式错误（应该是数组）")
        return False
    
    print(f"   📖 找到 {len(words)} 个单词")
    
    conn = connect_db()
    try:
        with conn.cursor() as cursor:
            # 检查是否已导入
            cursor.execute("SELECT COUNT(*) FROM dict_word WHERE book_id = %s", (book_id,))
            existing = cursor.fetchone()[0]
            
            if existing > 0:
                print(f"   ⏭️  已导入 {existing} 个单词，跳过")
                return 'skip'
            
            # 批量插入
            batch = []
            for i, w in enumerate(words, 1):
                word = w.get('name') or w.get('word', '')
                if not word:
                    continue
                
                # 处理标注（日语等语言）
                notation = w.get('notation', '')
                
                # 处理翻译
                trans = w.get('trans', [])
                if isinstance(trans, list):
                    translation = '; '.join(str(t) for t in trans)
                else:
                    translation = str(trans) if trans else ''
                
                batch.append((
                    book_id, word, notation, translation,
                    w.get('usphone', ''), w.get('ukphone', ''),
                    w.get('sentence') or w.get('example', ''),
                    w.get('definition') or w.get('meaning', ''),
                    i, '0', 'system'
                ))
                
                # 每500条批量插入
                if len(batch) >= 500:
                    cursor.executemany(
                        """INSERT INTO dict_word 
                        (book_id, word, notation, translation, phonetic_us, phonetic_uk, 
                         example_sentence, word_definition, sort_order, status, create_by, create_time) 
                        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW())""",
                        batch
                    )
                    batch = []
            
            # 插入剩余数据
            if batch:
                cursor.executemany(
                    """INSERT INTO dict_word 
                    (book_id, word, notation, translation, phonetic_us, phonetic_uk, 
                     example_sentence, word_definition, sort_order, status, create_by, create_time) 
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW())""",
                    batch
                )
            
            total = len(words)
            
            # 更新词典状态
            cursor.execute(
                "UPDATE dict_book SET is_loaded = '1', length = %s WHERE id = %s",
                (total, book_id)
            )
            
            conn.commit()
            print(f"   ✅ 成功导入 {total} 个单词")
            return True
            
    except Exception as e:
        conn.rollback()
        print(f"   ❌ 导入失败: {e}")
        return False
    finally:
        conn.close()

def main():
    print("=" * 80)
    print("词典单词自动导入工具")
    print("=" * 80)
    print()
    
    # 获取所有词典
    print("[1/3] 从数据库获取词典列表...")
    conn = connect_db()
    try:
        with conn.cursor(pymysql.cursors.DictCursor) as cursor:
            cursor.execute("""
                SELECT id, name, url, is_loaded 
                FROM dict_book 
                WHERE url IS NOT NULL AND url != ''
                ORDER BY name
            """)
            books = cursor.fetchall()
    finally:
        conn.close()
    
    if not books:
        print("❌ 没有找到词典记录")
        sys.exit(1)
    
    print(f"✓ 找到 {len(books)} 个词典")
    print()
    
    # 导入每个词典
    print("[2/3] 开始导入单词...")
    success = 0
    failed = 0
    skipped = 0
    
    for book in books:
        book_id = book['id']
        book_name = book['name']
        url = book['url']
        
        # 提取文件名
        filename = os.path.basename(url)
        json_file = os.path.join(DICT_PATH, filename)
        
        print(f"📚 {book_name}")
        print(f"   文件: {filename}")
        
        # 检查文件是否存在
        if not os.path.exists(json_file):
            print(f"   ⚠️  找不到文件")
            failed += 1
            continue
        
        # 导入
        result = import_dict_words(book_id, book_name, json_file)
        if result == True:
            success += 1
        elif result == 'skip':
            skipped += 1
        else:
            failed += 1
    
    print()
    print("[3/3] 导入完成！")
    print("=" * 80)
    print(f"📊 导入统计：")
    print(f"   ✅ 成功：{success} 个")
    print(f"   ⏭️  跳过：{skipped} 个")
    print(f"   ❌ 失败：{failed} 个")
    print("=" * 80)
    print()
    
    # 显示结果统计
    print("查看导入结果：")
    conn = connect_db()
    try:
        with conn.cursor() as cursor:
            cursor.execute("""
                SELECT 
                    b.id, 
                    b.name, 
                    b.is_loaded,
                    b.length as recorded_count,
                    COUNT(w.id) as actual_count
                FROM dict_book b
                LEFT JOIN dict_word w ON b.id = w.book_id
                GROUP BY b.id, b.name, b.is_loaded, b.length
                HAVING actual_count > 0
                ORDER BY actual_count DESC
                LIMIT 20
            """)
            results = cursor.fetchall()
            
            print()
            print(f"{'词典ID':<20} {'名称':<30} {'状态':<10} {'单词数'}")
            print("-" * 80)
            for row in results:
                status = '✅ 已导入' if row[2] == '1' else '  未导入'
                print(f"{row[0]:<20} {row[1]:<30} {status:<10} {row[4]}")
    finally:
        conn.close()
    
    print()
    print("✨ 完成！现在您的词典单词都已存储在数据库中了。")

if __name__ == '__main__':
    try:
        main()
    except KeyboardInterrupt:
        print("\n\n⚠️  用户中断操作")
        sys.exit(0)
    except Exception as e:
        print(f"\n❌ 发生错误: {e}")
        import traceback
        traceback.print_exc()
        sys.exit(1)

